﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WL_Store.Model;

namespace WL_Store.DAL
{
   public  class FeedbackDAl
    {
        /// <summary>
        /// 查询所有留言反馈
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        public List<FeedbackModel> GetFeedbacks(FeedbackSearchModel searchModel)
        {
            string sql = @"select f.Id, f.number,f.name,f.feedCount,f.feedTime,f.typeId,t.[type],f.[state],f.isDelete from Feedback as f inner join FeedbackTypes as t on f.typeId=t.typeId";

            //构造where的泛型集合
            List<string> listWhere = new List<string>();
            //构造参数化查询数组的泛型集合
            List<SqlParameter> listParameters = new List<SqlParameter>();

            if (!string.IsNullOrWhiteSpace(searchModel.name))
            {
                listWhere.Add("f.name=@name");
                listParameters.Add(new SqlParameter("@name",searchModel.name));
            }
            if (searchModel.StartFeedtime.HasValue)
            {
                listWhere.Add("f.feedTime>=@StartFeedtime");
                listParameters.Add(new SqlParameter("@StartFeedtime", searchModel.StartFeedtime));
            }
            if (searchModel.EndFeedtime.HasValue)
            {
                listWhere.Add("f.feedTime<=@EndFeedtime");
                listParameters.Add(new SqlParameter("@EndFeedtime", searchModel.EndFeedtime));
            }
            if (searchModel.typeId>0)
            {
                listWhere.Add("f.typeId=@typeId");
                listParameters.Add(new SqlParameter("@typeId", searchModel.typeId));
            }

            if (listWhere.Count>0)
            {
                sql += " where " + string.Join(" and ",listWhere.ToArray());
            }

           SqlDataReader reader= Dbhelper.ExecuteReader(sql,listParameters.ToArray());
            //构造出一个泛型集合
            List<FeedbackModel> list = new List<FeedbackModel>();
            //判断游标中是否有数据行
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    FeedbackModel model = new FeedbackModel();
                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.number = reader["number"].ToString();
                    model.name = reader["name"].ToString();
                    model.feedCount = reader["feedCount"].ToString();
                    model.feedTime = Convert.ToDateTime(reader["feedTime"]);
                    model.types = reader["type"].ToString();
                    model.state = Convert.ToBoolean(reader["state"]);
                    model.isDelete = Convert.ToBoolean(reader["isDelete"]);

                    //追加到集合中
                    list.Add(model);
                }
                //关闭游标
                reader.Close();
            }
            return list;
        }

        /// <summary>
        /// 查询类型
        /// </summary>
        /// <returns></returns>
        public DataTable GetTypes()
        {
            string sql = @"select*from FeedbackTypes";

            return Dbhelper.ExecuteTable(sql);
        }

        /// <summary>
        /// 添加反馈信息 返回：true添加成功；返回false：添加失败
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddFeedback(FeedbackModel model)
        {
            string sql = @"insert into Feedback(number,name,feedCount,feedTime,typeId,[state],isDelete) values
                        (@number,@name,@feedCount,@feedTime,@typeId,@state,@isDelete)";

            SqlParameter[] parameters = {
            new SqlParameter("@number",model.number),
            new SqlParameter("@name",model.name),
            new SqlParameter("@feedCount",model.feedCount),
            new SqlParameter("@feedTime",model.feedTime),
            new SqlParameter("@typeId",model.typeId),
            new SqlParameter("@state",model.state),
            new SqlParameter("@isDelete",model.isDelete)
            };

            return Dbhelper.ExecuteNonQuery(sql, parameters) > 0;

        }

        /// <summary>
        /// 根据Id删除反馈信息 返回true：删除成功；返回false：删除失败(软删除)
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public bool DeleteFeedback(int Id)
        {
            string sql = @"update Feedback set isDelete=1 where id=@Id";
            SqlParameter[] parameters = { new SqlParameter("@Id",Id)};
            return Dbhelper.ExecuteNonQuery(sql,parameters)>0;
        }

        /// <summary>
        /// 根据Id查询反馈信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public FeedbackModel GetFeedback(int Id)
        {
            string sql = @"select f.Id, f.number,f.name,f.feedCount,f.feedTime,f.typeId,f.[state],f.isDelete from Feedback as f where Id=@Id";
            SqlParameter[] parameters = { 
                new SqlParameter("@Id", Id)
            };

            SqlDataReader reader = Dbhelper.ExecuteReader(sql, parameters);
           
            //判断游标中是否有数据行
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    FeedbackModel model = new FeedbackModel();
                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.number = reader["number"].ToString();
                    model.name = reader["name"].ToString();
                    model.feedCount = reader["feedCount"].ToString();
                    model.feedTime = Convert.ToDateTime(reader["feedTime"]);
                    model.typeId = Convert.ToInt32(reader["typeId"]);
                    model.state = Convert.ToBoolean(reader["state"]);
                    model.isDelete = Convert.ToBoolean(reader["isDelete"]);

                    //追加到集合中
                    return model;
                }
                //关闭游标
                reader.Close();
            }
            return null;
        }

        /// <summary>
        /// 更新反馈信息 返回true：更新成功；返回false：更新失败
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateFeedback(FeedbackModel model)
        {
            string sql = @"update Feedback set number=@number,name=@name,feedCount=@feedCount,feedTime=@feedTime,typeId=@typeId,[state]=@state,isDelete=@state  where Id=@Id";

            SqlParameter[] parameters = { 
            new SqlParameter("@Id",model.Id),
            new SqlParameter("@number",model.number),
            new SqlParameter("@name",model.name),
            new SqlParameter("@feedCount",model.feedCount),
            new SqlParameter("@feedTime",model.feedTime),
            new SqlParameter("@typeId",model.typeId),
            new SqlParameter("@state",model.state),
            new SqlParameter("@isDelete",model.isDelete)
            };
            return Dbhelper.ExecuteNonQuery(sql, parameters) > 0;

        }
    }
}
